﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Collections;
using System.Data.SqlClient;

namespace CMS.Class
{
    public class clsDataBase
    {
        public clsDataBase() { }
        public ArrayList arrTexts = new ArrayList();//Luu cac du lieu
        public ArrayList arrKeys = new ArrayList();//luu cac khoa chinh
        private int id = 0;
        private static String _strQpWeb = Constant.Connec;
        private QpWebConnec.QpConnec connec = null;
        public int Id
        {
            get
            {
                return this.id;
            }
            set
            {
                this.id = value;
            }
        }

         #region "Cac nhom ham cap nhat du lieu su dung Proc"

        /// <summary>
        /// 
        /// Cập nhật dữ liệu
        /// </summary>
        /// <param name="commandText"></param>
        /// <param name="commandType"></param>
        /// <returns></returns>

        public Boolean Update(String commandText, CommandType commandType)
        {
            String[] paremeters = new string[arrTexts.Count];
            Object[] values = new Object[arrTexts.Count];
            int count = 0;
            foreach (Control ctrl in arrTexts)
            {
                paremeters[count] = ctrl.ID.Substring(3);
                if (ctrl is TextBox)
                {
                    TextBox mytxt = (TextBox)ctrl;
                    values[count] = mytxt.Text;
                }
                if (ctrl is CheckBox)
                {
                    CheckBox mytxt = (CheckBox)ctrl;
                    values[count] = mytxt.Checked;
                }
                if (ctrl is DropDownList)
                {
                    DropDownList mytxt = (DropDownList)ctrl;
                    values[count] = mytxt.SelectedValue.ToString();
                }

                if (ctrl is FileUpload)
                {
                    FileUpload mytxt = (FileUpload)ctrl;
                    values[count] = mytxt.FileName;
                }

              
                count++;
            }
            connec =new QpWebConnec.QpConnec(_strQpWeb);
            return connec.ExecuteNonQuery(paremeters, values, commandText, commandType);
        }

        /// <summary>
        /// Cập nhật dữ liệu có khóa chính là ID tự đong tăng
        /// </summary>
        /// <param name="ID"></param>
        /// <param name="commandText"></param>
        /// <param name="commandType"></param>
        /// <returns></returns>

        public Boolean Update(int ID, String commandText, CommandType commandType)
        {
            String[] paremeters = new string[arrTexts.Count];
            Object[] values = new Object[arrTexts.Count];
            int count = 0;
            foreach (Control ctrl in arrTexts)
            {
                paremeters[count] = ctrl.ID.Substring(3);
                if (ctrl is TextBox)
                {
                    TextBox mytxt = (TextBox)ctrl;
                    values[count] = mytxt.Text;
                }
                if (ctrl is CheckBox)
                {
                    CheckBox mytxt = (CheckBox)ctrl;
                    values[count] = mytxt.Checked;
                }
                if (ctrl is DropDownList)
                {
                    DropDownList mytxt = (DropDownList)ctrl;
                    values[count] = mytxt.SelectedValue.ToString();
                }

                if (ctrl is FileUpload)
                {
                    FileUpload mytxt = (FileUpload)ctrl;
                    values[count] = mytxt.FileName;
                }

               
                count++;
            }
            connec = new QpWebConnec.QpConnec(_strQpWeb);
            return connec.ExecuteNonQuery(ID, paremeters, values, commandText, commandType);
        }

        /// <summary>
        /// Cập nhật dữ liệu có khóa chính là ID tự đong tăng
        /// </summary>
        /// <param name="ID"></param>
        /// <param name="commandText"></param>
        /// <param name="commandType"></param>
        /// <returns></returns>
        public Boolean Update(String para, String ID, String commandText, CommandType commandType)
        {
            String[] paremeters = new string[arrTexts.Count];
            Object[] values = new Object[arrTexts.Count];
            int count = 0;
            foreach (Control ctrl in arrTexts)
            {
                paremeters[count] = ctrl.ID.Substring(3);
                if (ctrl is TextBox)
                {
                    TextBox mytxt = (TextBox)ctrl;
                    values[count] = mytxt.Text;
                }
                if (ctrl is CheckBox)
                {
                    CheckBox mytxt = (CheckBox)ctrl;
                    values[count] = mytxt.Checked;
                }
                if (ctrl is DropDownList)
                {
                    DropDownList mytxt = (DropDownList)ctrl;
                    values[count] = mytxt.SelectedValue.ToString();
                }

                if (ctrl is FileUpload)
                {
                    FileUpload mytxt = (FileUpload)ctrl;
                    values[count] = mytxt.FileName;
                }

             
                count++;
            }
            connec = new QpWebConnec.QpConnec(_strQpWeb);
            return connec.ExecuteNonQuery(para,ID, paremeters, values, commandText, commandType);
        }
        /// <summary>
        /// Hàm sửa khi có  file hình anh hoặc nhạc
        /// </summary>
        /// <param name="ID"></param>
        /// <param name="commandText"></param>
        /// <param name="commandType"></param>
        /// <param name="strPath"></param>
        /// <returns></returns>

        public Boolean Update(String commandText, CommandType commandType,String strPath)
        {
            String[] paremeters = new string[arrTexts.Count];
            Object[] values = new Object[arrTexts.Count];
            int count = 0;
            foreach (Control ctrl in arrTexts)
            {
                paremeters[count] = ctrl.ID.Substring(3);
                if (ctrl is TextBox)
                {
                    TextBox mytxt = (TextBox)ctrl;
                    values[count] = mytxt.Text;
                }
                if (ctrl is CheckBox)
                {
                    CheckBox mytxt = (CheckBox)ctrl;
                    values[count] = mytxt.Checked;
                }
                if (ctrl is DropDownList)
                {
                    DropDownList mytxt = (DropDownList)ctrl;
                    values[count] = mytxt.SelectedValue.ToString();
                }

                if (ctrl is FileUpload)
                {
                    FileUpload mytxt = (FileUpload)ctrl;
                    values[count] = strPath;
                }

              
                count++;
            }
            connec = new QpWebConnec.QpConnec(_strQpWeb);
            return connec.ExecuteNonQuery(paremeters, values, commandText, commandType);
        }

      #endregion "Ket thuc nhom ham"

        #region "Nhom ham cap nhat du lieu su dung truy van"
        
        /// <summary>
        /// Truyen bang can cap nhat lai du lieu
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>

        public Boolean Update(String tableName)
        {
            int L = 0;
            int nI = 0;
            L = arrTexts.Count;
            nI = arrKeys.Count;
            String[] paremeters = new string[L+nI];
            Object[] values = new Object[L + nI];
            int count = 0;
            String strSql = "";
            String strFilde = "";
            String strParameter = "";

            foreach (Control ctrl in arrTexts)
            {
                String iCol = ctrl.ID.Substring(3);
                paremeters[count] = ctrl.ID.Substring(3);
                
                if (strFilde == "")
                {
                    strFilde = iCol + "=@" + iCol;
                }
                else
                {
                    strFilde = strFilde + "," + iCol + "=@" + iCol;
                }

                if (ctrl is TextBox)
                {
                    TextBox mytxt = (TextBox)ctrl;
                    values[count] = mytxt.Text;
                }
                if (ctrl is CheckBox)
                {
                    CheckBox mytxt = (CheckBox)ctrl;
                    values[count] = mytxt.Checked;
                }
                if (ctrl is DropDownList)
                {
                    DropDownList mytxt = (DropDownList)ctrl;
                    values[count] = mytxt.SelectedValue.ToString();
                }

                //if (ctrl is FileUpload)
                //{
                //    FileUpload mytxt = (FileUpload)ctrl;
                //    values[count] = strPath;
                //}

              

           
                
                count++;
            }

            
            foreach (Control ctrl in arrKeys)
            {
                String iCol = ctrl.ID.Substring(3);
                paremeters[count] = ctrl.ID.Substring(3);

                if (strParameter == "")
                {
                    strParameter = iCol + "=@" + iCol;
                }
                else
                {
                    strParameter = strParameter + " and " + iCol + "=@" + iCol;
                }

                if (ctrl is TextBox)
                {
                    TextBox mytxt = (TextBox)ctrl;
                    values[count] = mytxt.Text;
                }
                if (ctrl is CheckBox)
                {
                    CheckBox mytxt = (CheckBox)ctrl;
                    values[count] = mytxt.Checked;
                }
                if (ctrl is DropDownList)
                {
                    DropDownList mytxt = (DropDownList)ctrl;
                    values[count] = mytxt.SelectedValue.ToString();
                }

                //if (ctrl is FileUpload)
                //{
                //    FileUpload mytxt = (FileUpload)ctrl;
                //    values[count] = strPath;
                //}

               

        

                count++;
            }

            strSql = strSql + "update " + tableName + " set " + strFilde + " where " + strParameter;
            connec = new QpWebConnec.QpConnec(_strQpWeb);
            return connec.ExecuteNonQuery(paremeters, values, strSql,CommandType.Text);
        }



        /// <summary>
        /// Truyen bang can cap nhat lai du lieu
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>

        public Boolean Update(String tableName, String strPath)
        {
            int L = 0;
            int nI = 0;
            L = arrTexts.Count;
            nI = arrKeys.Count;
            String[] paremeters = new string[L + nI];
            Object[] values = new Object[L + nI];
            int count = 0;
            String strSql = "";
            String strFilde = "";
            String strParameter = "";

            foreach (Control ctrl in arrTexts)
            {
                String iCol = ctrl.ID.Substring(3);
                paremeters[count] = ctrl.ID.Substring(3);

                if (strFilde == "")
                {
                    strFilde = iCol + "=@" + iCol;
                }
                else
                {
                    strFilde = strFilde + "," + iCol + "=@" + iCol;
                }

                if (ctrl is TextBox)
                {
                    TextBox mytxt = (TextBox)ctrl;
                    values[count] = mytxt.Text;
                }
                if (ctrl is CheckBox)
                {
                    CheckBox mytxt = (CheckBox)ctrl;
                    values[count] = mytxt.Checked;
                }
                if (ctrl is DropDownList)
                {
                    DropDownList mytxt = (DropDownList)ctrl;
                    values[count] = mytxt.SelectedValue.ToString();
                }

                if (ctrl is FileUpload)
                {
                    FileUpload mytxt = (FileUpload)ctrl;
                    values[count] = strPath;
                }

              

            

            }
                foreach (Control ctrl in arrKeys)
                {
                    String iCol = ctrl.ID.Substring(3);
                    paremeters[count] = ctrl.ID.Substring(3);

                    if (strParameter == "")
                    {
                        strParameter = iCol + "=@" + iCol;
                    }
                    else
                    {
                        strParameter = strParameter + " and " + iCol + "=@" + iCol;
                    }

                    if (ctrl is TextBox)
                    {
                        TextBox mytxt = (TextBox)ctrl;
                        values[count] = mytxt.Text;
                    }
                    if (ctrl is CheckBox)
                    {
                        CheckBox mytxt = (CheckBox)ctrl;
                        values[count] = mytxt.Checked;
                    }
                    if (ctrl is DropDownList)
                    {
                        DropDownList mytxt = (DropDownList)ctrl;
                        values[count] = mytxt.SelectedValue.ToString();
                    }

                    //if (ctrl is FileUpload)
                    //{
                    //    FileUpload mytxt = (FileUpload)ctrl;
                    //    values[count] = strPath;
                    //}

                 
                    count++;
                }

                strSql = strSql + "update " + tableName + " set " + strFilde + " where " + strParameter;

                connec = new QpWebConnec.QpConnec(_strQpWeb);
                return connec.ExecuteNonQuery(paremeters, values, strSql, CommandType.Text);
            
            }

    #endregion "Ket thuc nhom ham"


        #region "Cac nhom ham ghi du lieu su dung Proc"
        
        /// <summary>
        /// Cập nhật dữ liệu có khóa chính là ID tự đong tăng
        /// </summary>
        /// <param name="ID"></param>
        /// <param name="commandText"></param>
        /// <param name="commandType"></param>
        /// <returns></returns>
        public Boolean Insert(String para, String ID, String commandText, CommandType commandType)
        {
            String[] paremeters = new string[arrTexts.Count];
            Object[] values = new Object[arrTexts.Count];
            int count = 0;
            foreach (Control ctrl in arrTexts)
            {
                paremeters[count] = ctrl.ID.Substring(3);
                if (ctrl is TextBox)
                {
                    TextBox mytxt = (TextBox)ctrl;
                    values[count] = mytxt.Text;
                }
                if (ctrl is CheckBox)
                {
                    CheckBox mytxt = (CheckBox)ctrl;
                    values[count] = mytxt.Checked;
                }
                if (ctrl is DropDownList)
                {
                    DropDownList mytxt = (DropDownList)ctrl;
                    values[count] = mytxt.SelectedValue.ToString();
                }

                if (ctrl is FileUpload)
                {
                    FileUpload mytxt = (FileUpload)ctrl;
                    values[count] = mytxt.FileName;
                }

                count++;
            }
            connec = new QpWebConnec.QpConnec(_strQpWeb);
            return connec.ExecuteNonQuery(para, ID, paremeters, values, commandText, commandType);
        }
        /// <summary>
        /// Thêm cơ sở dữ liệu
        /// </summary>
        /// <param name="commandText"></param>
        /// <param name="commandType"></param>
        /// <returns></returns>
        public Boolean Insert(String commandText, CommandType commandType)
        {

            String[] paremeters = new string[arrTexts.Count];
            Object[] values = new Object[arrTexts.Count];
            int count = 0;
            foreach (Control ctrl in arrTexts)
            {
                paremeters[count] = ctrl.ID.Substring(3);
                if (ctrl is TextBox)
                {
                    TextBox mytxt = (TextBox)ctrl;
                    values[count] = mytxt.Text;
                }
                if (ctrl is CheckBox)
                {
                    CheckBox mytxt = (CheckBox)ctrl;
                    values[count] = mytxt.Checked;
                }
                if (ctrl is DropDownList)
                {
                    DropDownList mytxt = (DropDownList)ctrl;
                    values[count] = mytxt.SelectedValue.ToString();
                }

                if (ctrl is FileUpload)
                {
                    FileUpload mytxt = (FileUpload)ctrl;
                    values[count] = mytxt.FileName;
                }

           

                count++;
            }
            connec = new QpWebConnec.QpConnec(_strQpWeb);
            return connec.ExecuteNonQuery(paremeters, values, commandText, commandType);

        }

        /// <summary>
        /// Thêm cơ sở dữ liệu
        /// </summary>
        /// <param name="commandText"></param>
        /// <param name="commandType"></param>
        /// <returns></returns>
        public Boolean Insert(String commandText,params SqlParameter []arrParameter)
        {

            String[] paremeters = new string[arrTexts.Count];
            Object[] values = new Object[arrTexts.Count];
            int count = 0;
            foreach (Control ctrl in arrTexts)
            {
                paremeters[count] = ctrl.ID.Substring(3);
                if (ctrl is TextBox)
                {
                    TextBox mytxt = (TextBox)ctrl;
                    values[count] = mytxt.Text;
                }
                if (ctrl is CheckBox)
                {
                    CheckBox mytxt = (CheckBox)ctrl;
                    values[count] = mytxt.Checked;
                }
                if (ctrl is DropDownList)
                {
                    DropDownList mytxt = (DropDownList)ctrl;
                    values[count] = mytxt.SelectedValue.ToString();
                }

                if (ctrl is FileUpload)
                {
                    FileUpload mytxt = (FileUpload)ctrl;
                    values[count] = mytxt.FileName;
                }

              

                count++;
            }
            connec = new QpWebConnec.QpConnec(_strQpWeb);
            return connec.ExecuteNonQuery(paremeters, values, commandText,arrParameter);

        }

        /// <summary>
        /// Hàm thêm khi có file hình ảnh hoặc các file nhạc
        /// </summary>
        /// <param name="commandText"></param>
        /// <param name="commandType"></param>
        /// <param name="strPath"></param>
        /// <returns></returns>

        public Boolean Insert(String commandText, CommandType commandType,String strPath)
        {

            String[] paremeters = new string[arrTexts.Count];
            Object[] values = new Object[arrTexts.Count];
            int count = 0;
            foreach (Control ctrl in arrTexts)
            {
                paremeters[count] = ctrl.ID.Substring(3);
                if (ctrl is TextBox)
                {
                    TextBox mytxt = (TextBox)ctrl;
                    values[count] = mytxt.Text;
                }
                if (ctrl is CheckBox)
                {
                    CheckBox mytxt = (CheckBox)ctrl;
                    values[count] = mytxt.Checked;
                }
                if (ctrl is DropDownList)
                {
                    DropDownList mytxt = (DropDownList)ctrl;
                    values[count] = mytxt.SelectedValue.ToString();
                }

                if (ctrl is FileUpload)
                {
                    FileUpload mytxt = (FileUpload)ctrl;
                    values[count] = strPath;
                }

            

                count++;
            }
            connec = new QpWebConnec.QpConnec(_strQpWeb);
            return connec.ExecuteNonQuery(paremeters, values, commandText, commandType);

        }

    #endregion "Ket thuc nhom ham ghi du lieu"


        #region "Cac nhom ham ghi du lieu su dung cau truy van"
        
        /// <summary>
        /// Ham ghi du lieu truyen ten bang can them vao
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public Boolean Insert(String tableName)
        {

            String strSql = "";
            String strInto = "";
            String strvalue = "";

            String[] paremeters = new string[arrTexts.Count];
            Object[] values = new Object[arrTexts.Count];
            int count = 0;
            foreach (Control ctrl in arrTexts)
            {
                String iCol = ctrl.ID.Substring(3);
                paremeters[count] = ctrl.ID.Substring(3);
               
                if (strInto == "")
                {
                    strInto = iCol;
                    strvalue = "@" + iCol;
                }
                else
                {
                    strInto = strInto + "," + iCol;
                    strvalue = strvalue + ",@" + iCol;
                }
                if (ctrl is TextBox)
                {
                    TextBox mytxt = (TextBox)ctrl;
                    values[count] = mytxt.Text;
                }
                if (ctrl is CheckBox)
                {
                    CheckBox mytxt = (CheckBox)ctrl;
                    values[count] = mytxt.Checked;
                }
                if (ctrl is DropDownList)
                {
                    DropDownList mytxt = (DropDownList)ctrl;
                    values[count] = mytxt.SelectedValue.ToString();
                }

                //if (ctrl is FileUpload)
                //{
                //    FileUpload mytxt = (FileUpload)ctrl;
                //    values[count] = strPath;
                //}

         
              

                count++;
            }

            strSql = "insert into " + tableName + "(" + strInto + ") values(" + strvalue + ")";

            connec = new QpWebConnec.QpConnec(_strQpWeb);
            return connec.ExecuteNonQuery(paremeters, values,strSql,CommandType.Text);

        }

        /// <summary>
        /// Ham ghi du lieu truyen ten bang can them vao va co file up len
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public Boolean Insert(String tableName,String strPath)
        {

            String strSql = "";
            String strInto = "";
            String strvalue = "";

            String[] paremeters = new string[arrTexts.Count];
            Object[] values = new Object[arrTexts.Count];
            int count = 0;
            foreach (Control ctrl in arrTexts)
            {
                String iCol = ctrl.ID.Substring(3);
                paremeters[count] = ctrl.ID.Substring(3);

                if (strInto == "")
                {
                    strInto = iCol;
                    strvalue = "@" + iCol;
                }
                else
                {
                    strInto = strInto + "," + iCol;
                    strvalue = strvalue + ",@" + iCol;
                }
                if (ctrl is TextBox)
                {
                    TextBox mytxt = (TextBox)ctrl;
                    values[count] = mytxt.Text;
                }
                if (ctrl is CheckBox)
                {
                    CheckBox mytxt = (CheckBox)ctrl;
                    values[count] = mytxt.Checked;
                }
                if (ctrl is DropDownList)
                {
                    DropDownList mytxt = (DropDownList)ctrl;
                    values[count] = mytxt.SelectedValue.ToString();
                }

                if (ctrl is FileUpload)
                {
                    FileUpload mytxt = (FileUpload)ctrl;
                    values[count] = strPath;
                }

         

              

                count++;
            }

            strSql = "insert into " + tableName + "(" + strInto + ") values(" + strvalue + ")";

            connec = new QpWebConnec.QpConnec(_strQpWeb);
            return connec.ExecuteNonQuery(paremeters, values, strSql, CommandType.Text);

        }

        #endregion "Ket thuc cac nhom ham ghi du lieu su dung cau truy van"
        /// <summary>
        /// Xóa dữ liệu
        /// </summary>
        /// <param name="commandText"></param>
        /// <param name="commandType"></param>
        /// <returns></returns>


        public Boolean Delete(String commandText, CommandType commandType)
        {
            Boolean bBool = false;
            connec = new QpWebConnec.QpConnec(_strQpWeb);
            String paremerter = "ID";
            Object value = (Object)this.id;
            bBool = connec.ExecuteNonQuery(paremerter, value, commandText, commandType);
            return bBool;
        }

        /// <summary>
        /// Xóa dữ liệu theo khóa chính
        /// </summary>
        /// <param name="ID"></param>
        /// <param name="commandText"></param>
        /// <param name="commandType"></param>
        /// <returns></returns>
        public Boolean Delete(Object ID, String commandText, CommandType commandType)
        {
            Boolean bBool = false;
            connec = new QpWebConnec.QpConnec(_strQpWeb);
            String paremerter = "ID";

            bBool = connec.ExecuteNonQuery(paremerter, ID, commandText, commandType);
            return bBool;
        }

        //ham xoa 1 khao chinh
        public Boolean Delete(Object keys, String strTableName,String Col)
        {
            Boolean bBool = false;
            connec = new QpWebConnec.QpConnec(_strQpWeb);
            String sql = "delete from "+ strTableName + " where " + Col + "=@"+Col;
            bBool = connec.ExecuteNonQuery(Col, keys, sql, CommandType.Text);
            return bBool;
        }

        public Boolean Status(String proName, int ID, int Status)
        {
            Boolean bBool=false;
            connec = new QpWebConnec.QpConnec(_strQpWeb);
            bBool = connec.ExecuteNonQuery(proName,new SqlParameter("@ID",ID),new SqlParameter("@Status",Status));
            return bBool;
        }

        /// <summary>
        /// Kiểm tra khóa chính
        /// </summary>
        /// <param name="commandText"></param>
        /// <param name="commandType"></param>
        /// <returns></returns>

        public Boolean CheckKeys(String commandText, CommandType commandType)
        {
            Boolean bBool = false;
            int i = arrKeys.Count;
            String[] paremeters = new string[i];
            Object[] values = new Object[i];
            int count = 0;
            foreach (Control ctrl in arrKeys)
            {
                ctrl.EnableViewState = false;
                paremeters[count] = ctrl.ID.Substring(3);
                if (ctrl is TextBox)
                {
                    TextBox mytxt = (TextBox)ctrl;
                    values[count] = mytxt.Text;
                }
                if (ctrl is CheckBox)
                {
                    CheckBox mytxt = (CheckBox)ctrl;
                    values[count] = mytxt.Checked;
                }
                if (ctrl is DropDownList)
                {
                    DropDownList mytxt = (DropDownList)ctrl;
                    values[count] = mytxt.SelectedValue.ToString();
                }



                count++;
            }

            connec = new QpWebConnec.QpConnec(_strQpWeb);
            bBool = connec.sqlDataReader(paremeters, values, commandText, commandType);


            return bBool;
        }
    }
}